﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using BartonExtension;
using System.Web.Script.Services;
using ResponseObject;
using SqlServerWrapper;
using System.Data.SqlClient;
using System.Data;
using Model;
using ZKCache;

namespace WebSrvs.NET
{
    /// <summary>
    /// ZK_BackEndService 的摘要说明
    /// </summary>
    [System.Web.Script.Services.ScriptService]
    [WebService(Namespace = "http://zker.hk/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消注释以下行。 
    // [System.Web.Script.Services.ScriptService]
    public class ZK_BackEndService : System.Web.Services.WebService
    {

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string refreshCache()
        {   
            ZKResponse response = new ZKResponse();
            try
            {
                CacheCore.standardCache.refresh();
            }
            catch(Exception ex)
            {
                response.isProcessSuccess = false;
                response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                response.errMsg = ex.Message;
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string delete_store(int store_id)
        {
            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using (SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                {
                    cmd.CommandText = "proc_delete_store";
                    cmd.Parameters.Add("@store_id", SqlDbType.Int).Value = store_id;
                    cmd.Parameters.Add("@return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                    try
                    {
                        cmd.ExecuteNonQuery();
                        int returnVal = cmd.Parameters["@return"].Value.toInt32().Value;
                        if (returnVal == -1)
                        {
                            response.isProcessSuccess = false;
                            response.errCode = ErrorCodes.CODE_ERR_ON_DELETE_STORE_FAIL;
                            response.errMsg = ErrorMsgs.MSG_ERR_ON_DELETE_STORE_FAIL;
                        }
                    }
                    catch (Exception ex)
                    {
                        response.isProcessSuccess = false;
                        response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                        response.errMsg = ex.Message;
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string AddStore(int store_id,int store_type, string title, float frequency, float duration, float level,int mall_id,string in_mall_loc,float lat,float lng,string catalogs,string live_time,string desc,string tel)
        {
            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using (SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                {
                    cmd.CommandText = "proc_add_store_new";
                    try
                    {
                        cmd.Parameters.Add("@store_id", SqlDbType.Int).Value = store_id;
                        cmd.Parameters.Add("@store_title", SqlDbType.VarChar).Value = title;
                        cmd.Parameters.Add("@store_type", SqlDbType.VarChar).Value = store_type;
                        cmd.Parameters.Add("@store_frequency", SqlDbType.Float).Value = frequency;
                        cmd.Parameters.Add("@store_duration", SqlDbType.Float).Value = duration;
                        cmd.Parameters.Add("@level", SqlDbType.Float).Value = level;
                        cmd.Parameters.Add("@mall_id", SqlDbType.Int).Value = mall_id;
                        cmd.Parameters.Add("@in_mall_location", SqlDbType.VarChar).Value = in_mall_loc;
                        cmd.Parameters.Add("@lat", SqlDbType.Float).Value = lat;
                        cmd.Parameters.Add("@lng", SqlDbType.Float).Value = lng;
                        cmd.Parameters.Add("@live_time", SqlDbType.VarChar).Value = live_time.isNullOrEmpty() ? (object)DBNull.Value : live_time;
                        cmd.Parameters.Add("@desc", SqlDbType.VarChar).Value = desc.isNullOrEmpty() ? (object)DBNull.Value : desc;
                        cmd.Parameters.Add("@tel", SqlDbType.VarChar).Value = tel.isNullOrEmpty() ? (object)DBNull.Value : tel;
                        cmd.Parameters.Add("@catalogs", SqlDbType.VarChar).Value = catalogs.isNullOrEmpty() ? "#NA" : catalogs;
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        response.isProcessSuccess = false;
                        response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                        response.errMsg = ex.Message;
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string AddMall(int mall_id, string name, string street, float mall_lat, float mall_lon)
        {
            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using (SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                {
                    cmd.CommandText = "proc_mall_add";
                    cmd.Parameters.Add("@mall_id", SqlDbType.Int).Value = mall_id;
                    cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = name;
                    cmd.Parameters.Add("@street", SqlDbType.VarChar).Value = street;
                    cmd.Parameters.Add("@geo_lat", SqlDbType.Float).Value = mall_lat;
                    cmd.Parameters.Add("@geo_lon", SqlDbType.Float).Value = mall_lon;
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        response.isProcessSuccess = false;
                        response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                        response.errMsg = ex.Message;
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string LocationAvailabilityCheck(string region, string plaza_title, string in_plaza_location)
        {
            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using (SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                {
                    cmd.CommandText = "proc_check_availability_on_location";
                    cmd.Parameters.Add("@region", SqlDbType.VarChar).Value = region;
                    cmd.Parameters.Add("@plaza_title", SqlDbType.VarChar).Value = plaza_title;
                    cmd.Parameters.Add("@in_plaza_location", SqlDbType.VarChar).Value = in_plaza_location;
                    cmd.Parameters.Add("@return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                    try
                    {
                        cmd.ExecuteNonQuery();
                        int returnVal = cmd.Parameters["@return"].Value.toInt32().Value;
                        if (returnVal > 0)
                        {
                            response.isProcessSuccess = false;
                            response.errCode = ErrorCodes.CODE_ERR_ON_BACKEND_LOCATION_EXIST;
                            response.errMsg = ErrorMsgs.MSG_ERR_ON_BACKEND_LOCATION_EXIST;
                        }
                    }
                    catch (Exception ex)
                    {
                        response.isProcessSuccess = false;
                        response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                        response.errMsg = ex.Message;
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string ActivityInStoreExists(int store_id, string activity_detail, string activity_launch_time, string activity_end_time)
        {
            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using (SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                {
                    cmd.CommandText = "proc_check_availability_on_activity";
                    cmd.Parameters.Add("@store_id", SqlDbType.Int).Value = store_id;
                    cmd.Parameters.Add("@activity_detail", SqlDbType.VarChar).Value = activity_detail;
                    cmd.Parameters.Add("@launch_time", SqlDbType.DateTime).Value = DateTime.Parse(activity_launch_time);
                    cmd.Parameters.Add("@end_time", SqlDbType.DateTime).Value = DateTime.Parse(activity_end_time);
                    cmd.Parameters.Add("@return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                    try
                    {
                        cmd.ExecuteNonQuery();
                        int returnVal = cmd.Parameters["@return"].Value.toInt32().Value;
                        if (returnVal > 0)
                        {
                            response.isProcessSuccess = false;
                            response.errCode = ErrorCodes.CODE_ERR_ON_BACKEND_ACTIVITY_EXISTS;
                            response.errMsg = ErrorMsgs.MSG_ERR_ON_BACKEND_ACTIVITY_EXISTS;
                        }
                    }
                    catch (Exception ex)
                    {
                        response.isProcessSuccess = false;
                        response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                        response.errMsg = ex.Message;
                    }
                }
            }
            return response.ToJson();
        }
        
        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string BatchInsertStores(string batchJson)
        {
            byte[] bf = Convert.FromBase64String(batchJson);
            string js = System.Text.Encoding.UTF8.GetString(bf);
            List<NewStore> stores = js.FromJson<List<NewStore>>();
            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using(SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using(SqlTransaction trans = conn.BeginTransaction())
                {
                    using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                    {
                        cmd.CommandText = "proc_add_store_new";
                        cmd.Transaction = trans;
                        foreach(NewStore store in stores)
                        {
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add("@store_id", SqlDbType.Int).Value = store.ID;
                            cmd.Parameters.Add("@store_title", SqlDbType.VarChar).Value = store.title;
                            cmd.Parameters.Add("@store_type", SqlDbType.Int).Value = store.type;
                            cmd.Parameters.Add("@store_duration", SqlDbType.Float).Value = store.duration;
                            cmd.Parameters.Add("@level", SqlDbType.Float).Value = store.level;
                            cmd.Parameters.Add("@mall_code", SqlDbType.VarChar).Value = store.mall.code;
                            cmd.Parameters.Add("@in_mall_location", SqlDbType.VarChar).Value = store.in_mall_location;
                            string catalogs = "";
                            foreach(StoreCatalog catalog in store.catalogs)
                            {
                                string single_pending = "{0},{1},{2},{3}".FormatWith(catalog.catalog.ID, catalog.top_price, catalog.low_price,catalog.isFeatured ? 1 : 0);
                                catalogs += "{0};".FormatWith(single_pending);
                            }
                            catalogs = catalogs.TrimEnd(';');
                            cmd.Parameters.Add("@catalogs", SqlDbType.VarChar).Value = catalogs;
                            cmd.ExecuteNonQuery();
                        }
                        try
                        {
                            trans.Commit();
                        }
                        catch(Exception ex)
                        {
                            trans.Rollback();
                            response.isProcessSuccess = false;
                            response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                            response.errMsg = ex.Message;
                        }
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string BatchImportActivities(string batchJson)
        {
            byte[] bf = Convert.FromBase64String(batchJson);
            string js = System.Text.Encoding.UTF8.GetString(bf);
            List<Dictionary<string, string>> acts = js.FromJson<List<Dictionary<string, string>>>();
            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using (SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                    {
                        cmd.CommandText = "proc_add_activity_import";
                        cmd.Transaction = trans;
                        foreach (Dictionary<string, string> act in acts)
                        {
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add("@act_id", SqlDbType.Int).Value = act["act_id"].toInt32().Value;
                            cmd.Parameters.Add("@s_name", SqlDbType.VarChar).Value = act["s_name"];
                            cmd.Parameters.Add("@in_mall_location", SqlDbType.VarChar).Value = act["in_mall_location"];
                            cmd.Parameters.Add("@mall_code", SqlDbType.VarChar).Value = act["mall_code"];
                            cmd.Parameters.Add("@catalog_code", SqlDbType.VarChar).Value = act["c_code"];
                            cmd.Parameters.Add("@sale_desc", SqlDbType.VarChar).Value = act["sale_desc"];
                            cmd.Parameters.Add("@sale_title", SqlDbType.VarChar).Value = act["sale_title"];
                            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = act["status"].toInt32().Value;
                            cmd.Parameters.Add("@sale_rate", SqlDbType.Float).Value = act["sale_rate"].toFloat().Value;
                            cmd.Parameters.Add("@ori_price", SqlDbType.Float).Value = act["ori_price"].Equals("NA") ? (object)DBNull.Value : act["ori_price"].toFloat().Value;
                            cmd.Parameters.Add("@discount_rate", SqlDbType.Float).Value = act["d_rate"].toFloat().Value;
                            cmd.Parameters.Add("@time_from", SqlDbType.VarChar).Value = act["time_from"].isNullOrEmpty() ? (object)DBNull.Value : act["time_from"];
                            cmd.Parameters.Add("@time_to", SqlDbType.VarChar).Value = act["time_to"].isNullOrEmpty() ? (object)DBNull.Value : act["time_to"];
                            cmd.Parameters.Add("@on_show_type", SqlDbType.VarChar).Value = act["on_show_type"].toInt32().Value;
                            cmd.ExecuteNonQuery();
                        }
                        try
                        {
                            trans.Commit();
                        }
                        catch (Exception ex)
                        {
                            trans.Rollback();
                            response.isProcessSuccess = false;
                            response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                            response.errMsg = ex.Message;
                        }
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string BatchImportStores(string batchJson)
        {
            byte[] bf = Convert.FromBase64String(batchJson);
            string js = System.Text.Encoding.UTF8.GetString(bf);
            List<Dictionary<string, string>> stores = js.FromJson<List<Dictionary<string, string>>>();
            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            int err_count = 0;
            using (SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                    {
                        cmd.CommandText = "proc_add_store_import";
                        cmd.Transaction = trans;
                        foreach (Dictionary<string, string> store in stores)
                        {
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add("@store_id", SqlDbType.Int).Value = store["store_id"].toInt32().Value;
                            cmd.Parameters.Add("@store_title", SqlDbType.VarChar).Value = store["title"].HtmlDecode();
                            cmd.Parameters.Add("@store_type", SqlDbType.Int).Value = store["type"].toInt32().Value;
                            cmd.Parameters.Add("@store_frequency", SqlDbType.Float).Value = store["frequency"].toFloat().Value;
                            cmd.Parameters.Add("@store_duration", SqlDbType.Float).Value = store["duration"].toFloat().Value;
                            cmd.Parameters.Add("@level", SqlDbType.Float).Value = store["level"].toFloat().Value;
                            cmd.Parameters.Add("@mall_code", SqlDbType.VarChar).Value = store["mall_code"];
                            cmd.Parameters.Add("@in_mall_location", SqlDbType.VarChar).Value = store["in_mall_location"].HtmlDecode();
                            cmd.Parameters.Add("@lat", SqlDbType.Float).Value = store["lat"].toFloat().Value;
                            cmd.Parameters.Add("@lng", SqlDbType.Float).Value = store["lng"].toFloat().Value;
                            cmd.Parameters.Add("@live_time", SqlDbType.VarChar).Value = store["live_time"].isNullOrEmpty() ? (object)DBNull.Value : store["live_time"].HtmlDecode();
                            cmd.Parameters.Add("@catalogs", SqlDbType.VarChar).Value = store["catalogs"].isNullOrEmpty() ? "#NA" : store["catalogs"].HtmlDecode();
                            cmd.Parameters.Add("@desc", SqlDbType.VarChar).Value = store["desc"].ToString().Replace(":","：")
                                                                                                           .Replace(",","，")
                                                                                                           .Replace("{","{")
                                                                                                           .Replace("\"","”");
                            cmd.Parameters.Add("@tel", SqlDbType.VarChar).Value = store["tel"].isNullOrEmpty() ? (object)DBNull.Value : store["tel"].HtmlDecode();
                            try
                            {
                                cmd.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                response.isProcessSuccess = false;
                                response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                                response.errMsg = ex.Message;
                                err_count++;
                                break;
                            }
                        }
                        try
                        {
                            if (err_count == 0)
                                trans.Commit();
                            else
                                trans.Rollback();
                        }
                        catch (Exception ex)
                        {
                            trans.Rollback();
                            response.isProcessSuccess = false;
                            response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                            response.errMsg = ex.Message;
                        }
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string BatchInsertBrands(string batchJson)
        {
            //Convert from base64
            byte[] bf = Convert.FromBase64String(batchJson);
            string js = System.Text.Encoding.UTF8.GetString(bf);

            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using (SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    List<Dictionary<string, object>> brands = js.FromJson<List<Dictionary<string, object>>>();
                    foreach (Dictionary<string, object> brand in brands)
                    {
                        using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                        {
                            cmd.CommandText = "proc_insert_new_brand";
                            cmd.Transaction = trans;
                            cmd.Parameters.Add("@code", SqlDbType.NVarChar).Value = brand["code"].ToString().ToUpper();
                            cmd.Parameters.Add("@level", SqlDbType.Float).Value = brand["level"].toFloat().Value;
                            cmd.Parameters.Add("@title", SqlDbType.VarChar).Value = brand["title"].ToString();
                            cmd.Parameters.Add("@title_origin", SqlDbType.VarChar).Value = brand["title_origin"].ToString();
                            cmd.Parameters.Add("@location", SqlDbType.VarChar).Value = brand["location"].ToString();

                            byte[] buffer = Convert.FromBase64String(brand["desc"].ToString());
                            string desc = System.Text.Encoding.UTF8.GetString(buffer);
                            cmd.Parameters.Add("@desc", SqlDbType.VarChar).Value = desc;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    try
                    {
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        response.isProcessSuccess = false;
                        response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                        response.errMsg = ex.Message;
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string BatchInsertCatalogs(string batchJson)
        {
            //Decode base64 string 
            byte[] bf = Convert.FromBase64String(batchJson);
            string js = System.Text.Encoding.UTF8.GetString(bf);

            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using (SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    List<Dictionary<string, object>> catalogs = js.FromJson<List<Dictionary<string, object>>>();
                    foreach (Dictionary<string, object> catalog in catalogs)
                    {
                        using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                        {
                            cmd.CommandText = "proc_insert_new_catalog";
                            cmd.Transaction = trans;
                            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = catalog["ID"] == null ? (object)DBNull.Value : int.Parse(catalog["ID"].ToString());
                            cmd.Parameters.Add("@code", SqlDbType.NVarChar).Value = catalog["code"].ToString().ToUpper();
                            cmd.Parameters.Add("@title", SqlDbType.VarChar).Value = catalog["title"].ToString();
                            cmd.Parameters.Add("@title_en", SqlDbType.VarChar).Value = catalog["title_en"].ToString();
                            cmd.Parameters.Add("@parent_code", SqlDbType.VarChar).Value = string.IsNullOrEmpty(catalog["parent_code"].ToString()) ? (object)DBNull.Value : catalog["parent_code"].ToString();
                            cmd.Parameters.Add("@layer_num", SqlDbType.VarChar).Value = int.Parse(catalog["layer"].ToString());
                            cmd.ExecuteNonQuery();
                        }
                    }
                    try
                    {
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        response.isProcessSuccess = false;
                        response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                        response.errMsg = ex.Message;
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string AddAct(string jsonAct)
        {
            ZKResponse response = new ZKResponse();
            Activity act = jsonAct.FromJson<Activity>();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using(SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using(SqlCommand cmd = SqlCommandManager.Create(conn,CommandType.StoredProcedure))
                {
                    cmd.Parameters.Add("@activity_id", SqlDbType.Int).Value = act.ID;
                    cmd.Parameters.Add("@store_id", SqlDbType.Int).Value = act.store_id;
                    cmd.Parameters.Add("@catalog_ids", SqlDbType.Int).Value = act.catalog_ids;
                    cmd.Parameters.Add("@sale_rate", SqlDbType.Float).Value = act.sale_rate;
                    cmd.Parameters.Add("@status", SqlDbType.Bit).Value = act.status;
                    cmd.Parameters.Add("@sale_desc", SqlDbType.VarChar).Value = act.sale_desc;
                    cmd.Parameters.Add("@discount_rate", SqlDbType.Float).Value = act.discount_rate;
                    cmd.Parameters.Add("@time_from", SqlDbType.VarChar).Value = act.time_from;
                    cmd.Parameters.Add("@time_to", SqlDbType.VarChar).Value = act.time_to;
                    cmd.Parameters.Add("@images", SqlDbType.VarChar).Value = act.images;
                    cmd.Parameters.Add("@on_show_type", SqlDbType.Int).Value = act.on_show_type;
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch(Exception ex)
                    {
                        response.isProcessSuccess = false;
                        response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                        response.errMsg = ex.Message;
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string BatchInsertActs(string batchJson)
        {
            //Decode base64 string 
            byte[] bf = Convert.FromBase64String(batchJson);
            string js = System.Text.Encoding.UTF8.GetString(bf);

            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using (SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    List<Activity> acts = js.FromJson<List<Activity>>();
                        using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                        {
                            cmd.CommandText = "proc_add_activity";
                            cmd.Transaction = trans;
                            foreach (Activity act in acts)
                            {
                                cmd.Parameters.Clear();
                                cmd.Parameters.Add("@activity_id", SqlDbType.Int).Value = act.ID;
                                cmd.Parameters.Add("@store_id", SqlDbType.Int).Value = act.store_id;
                                cmd.Parameters.Add("@catalog_ids", SqlDbType.Int).Value = act.catalog_ids;
                                cmd.Parameters.Add("@sale_rate", SqlDbType.Float).Value = act.sale_rate;
                                cmd.Parameters.Add("@status", SqlDbType.Bit).Value = act.status;
                                cmd.Parameters.Add("@sale_desc", SqlDbType.VarChar).Value = act.sale_desc;
                                cmd.Parameters.Add("@discount_rate", SqlDbType.Float).Value = act.discount_rate;
                                cmd.Parameters.Add("@time_from", SqlDbType.VarChar).Value = act.time_from;
                                cmd.Parameters.Add("@time_to", SqlDbType.VarChar).Value = act.time_to;
                                cmd.Parameters.Add("@images", SqlDbType.VarChar).Value = act.images;
                                cmd.Parameters.Add("@on_show_type", SqlDbType.Int).Value = act.on_show_type;
                                cmd.ExecuteNonQuery();
                            }
                        }
                    try
                    {
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        response.isProcessSuccess = false;
                        response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                        response.errMsg = ex.Message;
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string BatchInsertMalls(string batchJson)
        {
            byte[] bf = Convert.FromBase64String(batchJson);
            string json = System.Text.Encoding.UTF8.GetString(bf);
            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using (SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    List<Mall> malls = json.FromJson<List<Mall>>();
                    foreach (Mall mall in malls)
                    {
                        using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                        {
                            cmd.CommandText = "proc_mall_add";
                            cmd.Transaction = trans;
                            cmd.Parameters.Add("@mall_id", SqlDbType.Int).Value = mall.ID;
                            cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = mall.name;
                            cmd.Parameters.Add("@region", SqlDbType.VarChar).Value = mall.region;
                            cmd.Parameters.Add("@street", SqlDbType.VarChar).Value = mall.street;
                            cmd.Parameters.Add("@code", SqlDbType.VarChar).Value = mall.code;
                            cmd.Parameters.Add("@geo_lat", SqlDbType.Float).Value = mall.latitude;
                            cmd.Parameters.Add("@geo_lon", SqlDbType.Float).Value = mall.longitude;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    try
                    {
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        response.isProcessSuccess = false;
                        response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                        response.errMsg = ex.Message;
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string NeedSync(SyncType type, string timeTag)
        {
            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using (SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                {
                    cmd.CommandText = "proc_need_sync";
                    cmd.Parameters.Add("@syncType", SqlDbType.Int).Value = type;
                    cmd.Parameters.Add("@remoteDataTimeTag", SqlDbType.DateTime).Value = timeTag.toDateTime().Value;
                    cmd.Parameters.Add("@return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                    try
                    {
                        cmd.ExecuteNonQuery();
                        response.userinfo = cmd.Parameters["@return"].Value;
                    }
                    catch (Exception ex)
                    {
                        response.isProcessSuccess = false;
                        response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                        response.errMsg = ex.Message;
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string DeleteBrand(int id)
        {
            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using (SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                {
                    cmd.CommandText = "proc_delete_brand";
                    cmd.Parameters.Add("@brand_id", SqlDbType.Int).Value = id;
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        response.isProcessSuccess = false;
                        response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                        response.errMsg = ex.Message;
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string DeleteCatalog(int id)
        {
            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using (SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                {
                    cmd.CommandText = "proc_delete_catalog";
                    cmd.Parameters.Add("@catalog_id", SqlDbType.Int).Value = id;
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        response.isProcessSuccess = false;
                        response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                        response.errMsg = ex.Message;
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string DeleteMall(int id)
        {
            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using (SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                {
                    cmd.CommandText = "proc_delete_mall";
                    cmd.Parameters.Add(@"id", SqlDbType.Int).Value = id;
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        response.isProcessSuccess = false;
                        response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                        response.errMsg = ex.Message;
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string BatchAddActivities(string json)
        {
            string js = json.fromBase64();
            List<Activity> activities = js.FromJson<List<Activity>>();
            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using(SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using(SqlTransaction trans = conn.BeginTransaction())
                {
                    using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                    {
                        cmd.CommandText = "proc_add_activity";
                        cmd.Transaction = trans;
                        foreach(Activity a in activities)
                        {
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add("@activity_id", SqlDbType.Int).Value = a.ID;
                            cmd.Parameters.Add("@store_id", SqlDbType.Int).Value = a.store_id;
                            cmd.Parameters.Add("@catalog_ids", SqlDbType.VarChar).Value = a.catalog_ids;
                            cmd.Parameters.Add("@sale_desc", SqlDbType.VarChar).Value = a.sale_rate;
                            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = a.status;
                            cmd.Parameters.Add("@sale_rate", SqlDbType.Float).Value = a.sale_rate;
                            cmd.Parameters.Add("@discount_rate", SqlDbType.Float).Value = a.discount_rate;
                            cmd.Parameters.Add("@time_from", SqlDbType.VarChar).Value = a.time_from;
                            cmd.Parameters.Add("@time_to", SqlDbType.VarChar).Value = a.time_to;
                            cmd.Parameters.Add("@images", SqlDbType.VarChar).Value = a.images;
                            cmd.Parameters.Add("@on_show_type", SqlDbType.Int).Value = a.on_show_type;
                            cmd.ExecuteNonQuery();
                        }
                        try
                        {
                            trans.Commit();
                        }
                        catch(Exception ex)
                        {
                            trans.Rollback();
                            response.isProcessSuccess = false;
                            response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                            response.errMsg = ex.Message;
                        }
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string GetAllStoresByMall(int mall_id)
        {
            ZKResponse respones = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using(SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using(SqlCommand cmd = SqlCommandManager.Create(conn,CommandType.StoredProcedure))
                {
                    cmd.CommandText = "proc_backend_get_shops";
                    cmd.Parameters.Add("@mall_id", SqlDbType.VarChar).Value = mall_id;
                    try
                    {
                        using(SqlDataReader dr = cmd.ExecuteReader())
                        {
                            List<NewStore> stores = new List<NewStore>();
                            
                            while(dr.Read())
                            {
                                int index = stores.FindIndex(s =>
                                {
                                    return s.ID == dr["ID"].toInt32().Value;
                                });
                                if (index != -1)
                                {
                                    if (dr["catalog_id"] != DBNull.Value)
                                    {
                                        StoreCatalog catalog = new StoreCatalog()
                                        {
                                            catalog = new Catalog()
                                            {
                                                ID = dr["catalog_id"].toInt32().Value,
                                                title = dr["catalog_title"].ToString(),
                                                title_en = dr["catalog_title_en"].ToString(),
                                                parent_code = dr["parent_code"].ToString(),
                                                layer = dr["layer"].toInt32().Value,
                                                code = dr["catalog_code"].ToString()
                                            },
                                            top_price = dr["top_price"].toFloat().Value,
                                            low_price = dr["low_price"].toFloat().Value,
                                            isFeatured = dr["featured"].toBoolean().Value
                                        };
                                        stores[index].catalogs.Add(catalog);
                                    }
                                }
                                else
                                {
                                    NewStore store = new NewStore() { catalogs = new List<StoreCatalog>() };
                                    store.ID = dr["ID"].toInt32().Value;
                                    store.title = dr["title"].ToString();
                                    store.type = (StoreType)dr["type"].toInt32().Value;
                                    store.frequency = dr["frequency"].toFloat().Value;
                                    store.duration = dr["duration"].toFloat().Value;
                                    store.level = dr["level"].toFloat().Value;
                                    store.live_time = dr["live_time"].ToString();
                                    store.tel = dr["tel"].ToString();
                                    store.desc = dr["desc"].ToString();
                                    Mall mall = new Mall();
                                    mall.ID = dr["mall_id"].toInt32().Value;
                                    mall.code = dr["mall_code"].ToString();
                                    mall.name = dr["name"].ToString();
                                    mall.region = dr["region"].ToString();
                                    mall.street = dr["street"].ToString();
                                    mall.latitude = dr["m_lat"].toFloat().Value;
                                    mall.longitude = dr["m_lon"].toFloat().Value;
                                    store.mall = mall;
                                    store.in_mall_location = dr["in_mall_location"].ToString();
                                    store.lat = dr["s_lat"].toFloat().Value;
                                    store.lon = dr["s_lon"].toFloat().Value;

                                    if (dr["catalog_id"] != DBNull.Value)
                                    {
                                        StoreCatalog catalog = new StoreCatalog()
                                        {
                                            catalog = new Catalog()
                                            {
                                                ID = dr["catalog_id"].toInt32().Value,
                                                title = dr["catalog_title"].ToString(),
                                                title_en = dr["catalog_title_en"].ToString(),
                                                parent_code = dr["parent_code"].ToString(),
                                                layer = dr["layer"].toInt32().Value,
                                                code = dr["catalog_code"].ToString()
                                            },
                                            top_price = dr["top_price"].toFloat().Value,
                                            low_price = dr["low_price"].toFloat().Value,
                                            isFeatured = dr["featured"].toBoolean().Value
                                        };
                                        store.catalogs.Add(catalog);
                                    }

                                    stores.Add(store);
                                }
                            }
                            respones.userinfo = stores;
                        }
                    }
                    catch(Exception ex)
                    {
                        respones.isProcessSuccess = false;
                        respones.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                        respones.errMsg = ex.Message;
                    }
                }
            }
            return respones.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string BackendRequestSaleInStore(int store_id)
        {
            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using (SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.StoredProcedure))
                {
                    cmd.CommandText = "proc_get_sale_from_store";
                    cmd.Parameters.Add("@store_id", SqlDbType.Int).Value = store_id;
                    try
                    {
                        List<Dictionary<string, object>> results = new List<Dictionary<string, object>>();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                Dictionary<string, object> result = new Dictionary<string, object>();
                                for (int i = 0; i < dr.FieldCount; i++)
                                {
                                    result.Add(dr.GetName(i), dr[i]);
                                }
                                results.Add(result);
                            }
                        }
                        response.userinfo = results;
                    }
                    catch (Exception ex)
                    {
                        response.isProcessSuccess = false;
                        response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                        response.errMsg = ex.Message;
                    }
                }
            }
            return response.ToJson();
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public string GenerateSeedImport(string json)
        {
            string js = json.fromBase64();
            List<Dictionary<string, object>> seeds = js.FromJson<List<Dictionary<string, object>>>();
            ZKResponse response = new ZKResponse();
            string connStr = SqlConnectionManager.GetConnStrFromConfig();
            using(SqlConnection conn = SqlConnectionManager.CreateSqlConnection(connStr))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    using (SqlCommand cmd = SqlCommandManager.Create(conn, CommandType.Text))
                    {
                        cmd.Transaction = trans;
                        cmd.CommandText = "truncate table generator_seed";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "insert into generator_seed(code,level,top_price,low_price) " +
                                          "values(@code,@level,@tp,@lp)";
                        foreach (Dictionary<string, object> seed in seeds)
                        {
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add("@code", SqlDbType.VarChar).Value = seed["code"].ToString();
                            Dictionary<string, object> data = seed["data"].ToString().FromJson<Dictionary<string, object>>();
                            data.Keys.ToList().ForEach(x =>{
                                var str = data[x].ToString().Trim();
                                if (!str.Equals(","))
                                {
                                    cmd.Parameters.Add("@level", SqlDbType.Float).Value = x.toFloat().Value;
                                    cmd.Parameters.Add("@tp", SqlDbType.Float).Value = data[x].ToString().Split(',')[1];
                                    cmd.Parameters.Add("@lp", SqlDbType.Float).Value = data[x].ToString().Split(',')[0];
                                    cmd.ExecuteNonQuery();

                                    cmd.Parameters.RemoveAt("@level");
                                    cmd.Parameters.RemoveAt("@tp");
                                    cmd.Parameters.RemoveAt("@lp");
                                }
                            });
                            
                        }
                        try
                        {
                            trans.Commit();
                        }
                        catch(Exception ex)
                        {
                            trans.Rollback();
                            response.isProcessSuccess = false;
                            response.errCode = ErrorCodes.CODE_ERR_INTERNAL_ERR;
                            response.errMsg = ex.Message;
                        }
                    }
                }
            }
            return response.ToJson();
        }
    }
}
